package com.lagou.testDBUtils;

import com.lagou.Utils.DruidUtils;
import com.lagou.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class DBUtilsDemo01 {
    //需求一  编写一个测试类，在该测试类中使用PrepareStatement 根据name和gender进行数据查询，并将结果打印在控制台。

    @Test
    public void testFindByName() throws SQLException {

        //1.建立Connection连接
        Connection connection= DruidUtils.getConnection();

        //2.创建预处理对象 编写SQL
        String sql="select * from employee where ename=? and gender=?";
        PreparedStatement ps=connection.prepareStatement(sql);

        //3.设置占位符
        ps.setString(1,"张三");
        ps.setString(2,"男");

        //4.获取结果集
        ResultSet resultSet=ps.executeQuery();

        //5.处理结果集
        while(resultSet.next()){
            int eid=resultSet.getInt("eid");
            String ename=resultSet.getString("ename");
            String gender=resultSet.getString("gender");
            Double salary=resultSet.getDouble("salary");
            Double bonus=resultSet.getDouble("bonus");
            Date join_date=resultSet.getDate("join_date");

            //6.打印
            System.out.println("编号:"+eid+",姓名:"+ename+",性别:"+gender+",薪水:"+salary+",奖金:"+bonus+",入职日期:"+join_date);
        }
    }


    //需求二 .要求自己编写一个工具类DBUtils , 该类中提供静态获取Connection连接的方法.
    @Test
    public void testFindByGender() throws SQLException {

        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from employee where ename=? and gender=?";
        Object[] param={"张三","男"};
        List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class), param);
        for(Employee employee:list){
            System.out.println(employee);
        }
    }
}
